using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections;

using EntLibEC.Cmn;
using EntLibEC.Info.Online;

namespace EntLibEC.Dac.Online
{
	/// <summary>
	/// Summary description for AdvQuestionItemDac.
	/// </summary>
	public class AdvQuestionItemDac
	{
		
		public AdvQuestionItemDac()
		{
			
		}
		public int Insert(AdvQuestionItemInfo oParam)
		{
			string sql = @"INSERT INTO Ent_Adv_Question_Item
                            (
                            ActivitySysNo, Question, QuestionType, 
                            Option1, Option2, Option3, Option4, 
                            Option5, Option6, Answer
                            )
                            VALUES (
                            @ActivitySysNo, @Question, @QuestionType, 
                            @Option1, @Option2, @Option3, @Option4, 
                            @Option5, @Option6, @Answer
                            )";			
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramActivitySysNo = new SqlParameter("@ActivitySysNo", SqlDbType.Int,4);
			SqlParameter paramQuestion = new SqlParameter("@Question", SqlDbType.VarChar,100);
			SqlParameter paramQuestionType = new SqlParameter("@QuestionType", SqlDbType.Int,4);
			SqlParameter paramOption1 = new SqlParameter("@Option1", SqlDbType.VarChar,100);
			SqlParameter paramOption2 = new SqlParameter("@Option2", SqlDbType.VarChar,100);
			SqlParameter paramOption3 = new SqlParameter("@Option3", SqlDbType.VarChar,100);
			SqlParameter paramOption4 = new SqlParameter("@Option4", SqlDbType.VarChar,100);
			SqlParameter paramOption5 = new SqlParameter("@Option5", SqlDbType.VarChar,100);
			SqlParameter paramOption6 = new SqlParameter("@Option6", SqlDbType.VarChar,100);
			SqlParameter paramAnswer = new SqlParameter("@Answer", SqlDbType.VarChar,10);

			if ( oParam.SysNo != AppConst.IntNull)
				paramSysNo.Value = oParam.SysNo;
			else
				paramSysNo.Value = System.DBNull.Value;
			if ( oParam.ActivitySysNo != AppConst.IntNull)
				paramActivitySysNo.Value = oParam.ActivitySysNo;
			else
				paramActivitySysNo.Value = System.DBNull.Value;
			if ( oParam.Question != AppConst.StringNull)
				paramQuestion.Value = oParam.Question;
			else
				paramQuestion.Value = System.DBNull.Value;
			if ( oParam.QuestionType != AppConst.IntNull)
				paramQuestionType.Value = oParam.QuestionType;
			else
				paramQuestionType.Value = System.DBNull.Value;
			if ( oParam.Option1 != AppConst.StringNull)
				paramOption1.Value = oParam.Option1;
			else
				paramOption1.Value = System.DBNull.Value;
			if ( oParam.Option2 != AppConst.StringNull)
				paramOption2.Value = oParam.Option2;
			else
				paramOption2.Value = System.DBNull.Value;
			if ( oParam.Option3 != AppConst.StringNull)
				paramOption3.Value = oParam.Option3;
			else
				paramOption3.Value = System.DBNull.Value;
			if ( oParam.Option4 != AppConst.StringNull)
				paramOption4.Value = oParam.Option4;
			else
				paramOption4.Value = System.DBNull.Value;
			if ( oParam.Option5 != AppConst.StringNull)
				paramOption5.Value = oParam.Option5;
			else
				paramOption5.Value = System.DBNull.Value;
			if ( oParam.Option6 != AppConst.StringNull)
				paramOption6.Value = oParam.Option6;
			else
				paramOption6.Value = System.DBNull.Value;
			if ( oParam.Answer != AppConst.StringNull)
				paramAnswer.Value = oParam.Answer;
			else
				paramAnswer.Value = System.DBNull.Value;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramActivitySysNo);
			cmd.Parameters.Add(paramQuestion);
			cmd.Parameters.Add(paramQuestionType);
			cmd.Parameters.Add(paramOption1);
			cmd.Parameters.Add(paramOption2);
			cmd.Parameters.Add(paramOption3);
			cmd.Parameters.Add(paramOption4);
			cmd.Parameters.Add(paramOption5);
			cmd.Parameters.Add(paramOption6);
			cmd.Parameters.Add(paramAnswer);

			return SqlHelper.ExecuteNonQuery(cmd);
		}

		public int Update(Hashtable paramHash)
		{
			StringBuilder sb = new StringBuilder(200);
			sb.Append("UPDATE Ent_Adv_Question_Item SET ");

			if ( paramHash != null && paramHash.Count != 0 )
			{
				int index = 0;
				foreach(string key in paramHash.Keys)
				{
					object item = paramHash[key];
					if ( key.ToLower()=="sysno" )
						continue;

					if ( index != 0 )
						sb.Append(",");
					index++;

					
					if (item is int || item is decimal)
					{
						sb.Append(key).Append("=").Append(item.ToString());
					}
					else if (item is string)
					{
						sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
					}
					else if (item is DateTime)
					{
						sb.Append(key).Append(" = cast(").Append( Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
					}
				}
			}

			sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

			return SqlHelper.ExecuteNonQuery(sb.ToString());
		}

		public int Delete(int sysno)
		{
		    string sql = "delete from Ent_Adv_Question_item where sysno=" + sysno;
			SqlCommand cmd = new SqlCommand(sql);

			return SqlHelper.ExecuteNonQuery(cmd);
		}

		public int DeleteByActivitySysNo(int sysno)
		{
		    string sql = "delete from Ent_Adv_Question_item where activitysysno = " + sysno;
			SqlCommand cmd = new SqlCommand(sql);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
	}
}
